package com.lynn.salary.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.lynn.salary.dto.AccessorySearchDTO;
import com.lynn.salary.dto.ArticleSearchDTO;
import com.lynn.salary.dto.SubjectSearchDTO;
import com.lynn.salary.dto.UserSearchDTO;
import com.lynn.salary.service.ISearchService;
import com.lynn.salary.util.ToolMethod;

public class SearchService extends BaseService implements ISearchService {

	public List<Map<String, Object>> searchForUser(Object searchCondition) throws Exception {
		
		UserSearchDTO dto = (UserSearchDTO) searchCondition;
		
		StringBuffer sql = new StringBuffer("select e.id,e.name,e.gender,e.birthday,e.tag,e.registerTime,e.email from user_ e where 1=1 ");
		StringBuffer qtotal = new StringBuffer("select count(e.id) totalCount from user_ e where 1=1 ");
		
		List<Object> values = new ArrayList<Object>();
		
		StringBuffer sqlCondition = new StringBuffer();
		if(dto.getName()!=null &&!dto.getName().equals("")){
			sqlCondition.append("  and e.name like ?"); //名字过滤
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getName())).append("%").toString());
		}
		if(dto.getEmail()!=null &&!dto.getEmail().equals("")){
			sqlCondition.append(" and ")
			.append("e.email like ?")//邮件过滤
			;
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getEmail())).append("%").toString());
		}
		if(dto.getGender() != null) {
			sqlCondition.append(" and ")
			.append("e.gender in (").append(dto.getGender()).append(")")//性别过滤
			;
		}
		if(dto.getTag() != null) {
			sqlCondition.append(" and ")
			.append("e.tag in (").append(dto.getTag()).append(")")//状态过滤
			;
		}
		
		Date before = dto.getRegTimeBefore();
		Date after = dto.getRegTimeAfter();
		
		if(before != null) {
			String beforeS = ToolMethod.formatDate(before, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(e.registerTime,'%Y-%m-%d') < '")
			.append(beforeS).append("'")
			;
		}
		
		if(after != null) {
			String afterS = ToolMethod.formatDate(after, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(e.registerTime,'%Y-%m-%d') > '")
			.append(afterS).append("'")
			;
		}
		
		sql.append(sqlCondition)
		.append(" limit ").append(dto.getStartIdx()).append(",").append(dto.getNumPerPage());
		
		qtotal.append(sqlCondition);
		
		List<Map<String,Object>> data = dao.prepareExecuteSQL(sql.toString(),values.toArray());
		List<Map<String,Object>> tdata = dao.prepareExecuteSQL(qtotal.toString(),values.toArray());
		
		data.add(0, tdata.get(0));
		
		return data;
	}
	
	public List<Map<String, Object>> searchForArticle(Object searchCondition) throws Exception {
		
		ArticleSearchDTO dto = (ArticleSearchDTO) searchCondition;
		
		StringBuffer sql = new StringBuffer("select a.id,a.title,a.createTime,u.name name,atp.name type,a.tag "+
			"	from article a ,user_ u, articletype atp "+
			"	where a.type=atp.id and a.author=u.id and "+
			"	 a.isdeleted=0 ");
		StringBuffer qtotal = new StringBuffer("select count(a.id) totalCount from article a where a.isdeleted=0 ");
		
		List<Object> values = new ArrayList<Object>();
		
		StringBuffer sqlCondition = new StringBuffer();
		if(dto.getTitle()!=null &&!dto.getTitle().equals("")){
			sqlCondition.append("  and a.title like ?"); //标题过滤
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getTitle())).append("%").toString());
		}
		if(dto.getType()!=null &&!dto.getType().equals("")){
			sqlCondition.append(" and ")
			.append("a.type like ?")//文章类型过滤
			;
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getType())).append("%").toString());
		}
		if(dto.getTag() != null) {
			sqlCondition.append(" and ")
			.append("a.tag in (").append(dto.getTag()).append(")")//状态过滤
			;
		}
		
		Date before = dto.getCtTimeBefore();
		Date after = dto.getCtTimeAfter();
		
		if(before != null) {
			String beforeS = ToolMethod.formatDate(before, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(a.createTime,'%Y-%m-%d') < '")
			.append(beforeS).append("'")
			;
		}
		
		if(after != null) {
			String afterS = ToolMethod.formatDate(after, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(a.createTime,'%Y-%m-%d') > '")
			.append(afterS).append("'")
			;
		}
		
		sql.append(sqlCondition)
		.append(" order by a.createTime desc limit ").append(dto.getStartIdx()).append(",").append(dto.getNumPerPage());
		
		qtotal.append(sqlCondition);
		
		List<Map<String,Object>> data = dao.prepareExecuteSQL(sql.toString(),values.toArray());
		List<Map<String,Object>> tdata = dao.prepareExecuteSQL(qtotal.toString(),values.toArray());
		
		data.add(0, tdata.get(0));
		
		return data;
	}
	
	public List<Map<String, Object>> searchForSubject(Object searchCondition) throws Exception {
		
		SubjectSearchDTO dto = (SubjectSearchDTO) searchCondition;
		
		StringBuffer sql = new StringBuffer("select s.id,s.title,s.createtime,s.lasteCommentTime,u.name author,s.visitCount,s.commentCount,s.tag "+
				"from subject_ s left join user_ u "+
				"on s.author=u.id where s.isdeleted=0 and s.level_=0");
		StringBuffer qtotal = new StringBuffer("select count(s.id) totalCount "+
				"from subject_ s left join user_ u "+
				"on s.author=u.id where s.isdeleted=0 and s.level_=0 ");
		
		List<Object> values = new ArrayList<Object>();
		
		StringBuffer sqlCondition = new StringBuffer();
		if(dto.getTitle()!=null &&!dto.getTitle().equals("")){
			sqlCondition.append("  and s.title like ?"); //标题过滤
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getTitle())).append("%").toString());
		}
		if(dto.getAuthor()!=null &&!dto.getAuthor().equals("")){
			sqlCondition.append(" and ")
			.append("u.name like ?")//作者名字过滤
			;
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getAuthor())).append("%").toString());
		}
		if(dto.getVisitCount() != null) {
			sqlCondition.append(" and ")
			.append("s.visitCount > ?")//浏览次数过滤
			;
			values.add(dto.getVisitCount());
		}
		if(dto.getCommentCount() != null) {
			sqlCondition.append(" and ")
			.append("s.commentCount > ?")//评论次数过滤
			;
			values.add(dto.getCommentCount());
		}
		
		Date before = dto.getCtTimeBefore();
		Date after = dto.getCtTimeAfter();
		
		if(before != null) {
			String beforeS = ToolMethod.formatDate(before, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(s.createTime,'%Y-%m-%d') < '")
			.append(beforeS).append("'")
			;
		}
		
		if(after != null) {
			String afterS = ToolMethod.formatDate(after, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(s.createTime,'%Y-%m-%d') > '")
			.append(afterS).append("'")
			;
		}
		
		sql.append(sqlCondition)
		.append(" order by s.createTime desc limit ").append(dto.getStartIdx()).append(",").append(dto.getNumPerPage());
		
		qtotal.append(sqlCondition);
		
		List<Map<String,Object>> data = dao.prepareExecuteSQL(sql.toString(),values.toArray());
		List<Map<String,Object>> tdata = dao.prepareExecuteSQL(qtotal.toString(),values.toArray());
		
		data.add(0, tdata.get(0));
		
		return data;
	}
	public List<Map<String, Object>> searchForSubjectComment(Object searchCondition) throws Exception {
		
		SubjectSearchDTO dto = (SubjectSearchDTO) searchCondition;
		
		StringBuffer sql = new StringBuffer("select s.id,s.title,s.createtime,s.lasteCommentTime,u.name author,s.visitCount,s.commentCount,s.tag,s.level_ "+
				"from subject_ s left join user_ u "+
				"on s.author=u.id where s.isdeleted=0 and s.level_<>0");
		StringBuffer qtotal = new StringBuffer("select count(s.id) totalCount "+
				"from subject_ s left join user_ u "+
				"on s.author=u.id where s.isdeleted=0 and s.level_<>0 ");
		
		List<Object> values = new ArrayList<Object>();
		
		StringBuffer sqlCondition = new StringBuffer();
		if(dto.getTitle()!=null &&!dto.getTitle().equals("")){
			sqlCondition.append("  and s.title like ?"); //标题过滤
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getTitle())).append("%").toString());
		}
		if(dto.getAuthor()!=null &&!dto.getAuthor().equals("")){
			sqlCondition.append(" and ")
			.append("u.name like ?")//作者名字过滤
			;
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getAuthor())).append("%").toString());
		}
		if(dto.getVisitCount() != null) {
			sqlCondition.append(" and ")
			.append("s.visitCount > ?")//浏览次数过滤
			;
			values.add(dto.getVisitCount());
		}
		if(dto.getCommentCount() != null) {
			sqlCondition.append(" and ")
			.append("s.commentCount > ?")//评论次数过滤
			;
			values.add(dto.getCommentCount());
		}
		
		Date before = dto.getCtTimeBefore();
		Date after = dto.getCtTimeAfter();
		
		if(before != null) {
			String beforeS = ToolMethod.formatDate(before, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(s.createTime,'%Y-%m-%d') < '")
			.append(beforeS).append("'")
			;
		}
		
		if(after != null) {
			String afterS = ToolMethod.formatDate(after, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(s.createTime,'%Y-%m-%d') > '")
			.append(afterS).append("'")
			;
		}
		
		sql.append(sqlCondition)
		.append(" order by s.createTime desc limit ").append(dto.getStartIdx()).append(",").append(dto.getNumPerPage());
		
		qtotal.append(sqlCondition);
		
		List<Map<String,Object>> data = dao.prepareExecuteSQL(sql.toString(),values.toArray());
		List<Map<String,Object>> tdata = dao.prepareExecuteSQL(qtotal.toString(),values.toArray());
		
		data.add(0, tdata.get(0));
		
		return data;
	}
	

	public List<Map<String, Object>> searchForAccessory(Object searchCondition) throws Exception {
		
		AccessorySearchDTO dto = (AccessorySearchDTO) searchCondition;
		
		StringBuffer sql = new StringBuffer("select s.id,s.name sname,s.newTime,s.alterTime,u.name author,s.description " +
				"from accessory s left join user_ u "+
				"on s.creator=u.id where s.isdeleted=0 ");
		StringBuffer qtotal = new StringBuffer("select count(*) totalCount from accessory s where s.isdeleted=0 ");
		
		List<Object> values = new ArrayList<Object>();
		
		StringBuffer sqlCondition = new StringBuffer();
		if(dto.getName()!=null &&!dto.getName().equals("")){
			sqlCondition.append("  and s.name like ?"); //标题过滤
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getName())).append("%").toString());
		}
		if(dto.getDescription()!=null &&!dto.getDescription().equals("")){
			sqlCondition.append(" and ")
			.append("s.description like ?")//作者名字过滤
			;
			values.add(new StringBuffer("%").append(ToolMethod.escSpecChar(dto.getDescription())).append("%").toString());
		}
		
		Date before = dto.getCtTimeBefore();
		Date after = dto.getCtTimeAfter();
		
		if(before != null) {
			String beforeS = ToolMethod.formatDate(before, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(s.newTime,'%Y-%m-%d') < '")
			.append(beforeS).append("'")
			;
		}
		
		if(after != null) {
			String afterS = ToolMethod.formatDate(after, "yyyy-MM-dd");
			sqlCondition.append(" and ")
			.append(" date_format(s.newTime,'%Y-%m-%d') > '")
			.append(afterS).append("'")
			;
		}
		
		sql.append(sqlCondition)
		.append(" order by s.alterTime desc limit ").append(dto.getStartIdx()).append(",").append(dto.getNumPerPage());
		
		qtotal.append(sqlCondition);
		
		List<Map<String,Object>> data = dao.prepareExecuteSQL(sql.toString(),values.toArray());
		List<Map<String,Object>> tdata = dao.prepareExecuteSQL(qtotal.toString(),values.toArray());
		
		data.add(0, tdata.get(0));
		
		return data;
	}

}
